package zhaolu.com.example.Delicacy.MySQL;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import zhaolu.com.example.Delicacy.Entity.Customer;

public class CustomerDAO extends MySQLHelper {
    public List<Customer> getCustomerList() {
        List<Customer> list = new ArrayList<>();
        try {
            getConnection();
            String sql = "select * from Customer";
            callableStatement = connection.prepareCall(sql);
            resultSet = callableStatement.executeQuery();
            while (resultSet.next()) {
                Customer item = new Customer();
                item.setId(resultSet.getString("idCustomer"));
                item.setPassword(resultSet.getString("password"));
                item.setPoints(resultSet.getInt("points"));
                list.add(item);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

    public Customer getCustomerByUserAndPassword(String id, String password) {
        Customer item = null;
        try {
            getConnection();
            String sql = "select * from Customer where idCustomer=? and password=?";
            callableStatement = connection.prepareCall(sql);
            callableStatement.setString(1, id);
            callableStatement.setString(2, password);
            resultSet = callableStatement.executeQuery();
            if (resultSet.next()) {
                item = new Customer();
                item.setId(resultSet.getString("idCustomer"));
                item.setPassword(password);
                item.setPoints(resultSet.getInt("points"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return item;
    }

    public int addCustomer(Customer item) {
        int row = 0;
        try {
            getConnection();
            // 这里需要加一个判断：用户是否被注册过
            String sql = "insert into Customer(idCustomer, password, points) values(?, ?, ?)";
            callableStatement = connection.prepareCall(sql);
            callableStatement.setString(1, item.getId());
            callableStatement.setString(2, item.getPassword());
            callableStatement.setString(3, "0");
            row = callableStatement.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return row;
    }

    public void editCustomer(Customer item) {
        try {
            getConnection();
            // String sql = "update Customer set password=? where idCustomer=?";
            String sql = "call change_pwd(?,?)";
            callableStatement = connection.prepareCall(sql);
            callableStatement.setString(1, item.getId());
            callableStatement.setString(2, item.getPassword());
            callableStatement.execute();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
    }

    public int deleteCustomer(int id) {
        int iRow = 0;
        try {
            getConnection();
            String sql = "delete from Customer where idCustomer=?";
            callableStatement = connection.prepareCall(sql);
            callableStatement.setInt(1, id);
            iRow = callableStatement.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return iRow;
    }

    public int query_points(Customer item) {
        int points = 0;
        try {
            getConnection();
            String sql = "call query_points(?, ?)";
            callableStatement = connection.prepareCall(sql);
            callableStatement.setString(1, item.getId());
            callableStatement.registerOutParameter(2, Types.INTEGER);
            callableStatement.execute();
            points = callableStatement.getInt(2);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return points;
    }

    public void delete_user(Customer item) {
        try {
            getConnection();
            String sql = "call delete_user(?)";
            callableStatement = connection.prepareCall(sql);
            callableStatement.setString(1, item.getId());
            callableStatement.execute();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
    }

    public void test(Customer item) {
        try {
            getConnection();
            String sql = "update customer set points = ? where idCustomer = ?";
            callableStatement = connection.prepareCall(sql);
            callableStatement.setInt(1, item.getPoints());
            callableStatement.setString(2, item.getId());
            callableStatement.execute();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
    }
}
